const db = require('../utils/db')
const xlsx = require('node-xlsx')
const path = require('path')
const fs = require("fs")
const { Blob, Buffer } = require('node:buffer');
const { selectStudentScore } = require('./score.js')
const { selectAllClass, selectAllCourse } = require("./public_db")

const findAllCourse = () => {
  return new Promise((reosolve, reject) => {
    db.query(`select * from t_course`, (err, result) => {
      if (err) reject("出错了，请稍后重试")
      let arr = []
      for (let i in result) {
        arr.push(result[i].name)
        arr.push(null)
      }
      reosolve(arr)
    })
  })
}

const fileGetStudents = async (req, res) => {
  // 获取列表
  const data = await getListItem(req, res);
  console.log(data);
  var result = [];
  var title = ['学生编号', '学生姓名', '班级编号', '学生班级', '家长电话', '家长姓名', '成绩表']//这是第一行 俗称列名c
  // title.push("")
  result.push(title);
  let excelPath = path.resolve(__dirname, '../exportFiles');
  if (data.length == 0) {
    // 查询所有科目 
    const d = await findAllCourse();
    d.unshift("不填", null, "不填", null, null, "不填")
    result.push(d)
  } else {
    const dataMap = await data.map(async (item) => {
      return new Promise(async (resolve, reject) => {
        // var itemArr = [];
        // itemArr.push(item.id             );
        // itemArr.push(item.name        );
        // itemArr.push(item.c_id        );
        // itemArr.push(item.className     );
        // itemArr.push(item.p_phone       );
        // itemArr.push(item.p_name      );
        // result.push(itemArr);
        const itemData = await selectStudentScore(item.id)
        // console.log(itemData);
        let arr = [
          item.id,
          item.name,
          item.c_id,
          item.className,
          item.p_phone,
          item.p_name,
        ]
        itemData.forEach(e => {
          arr.push(e.courseName)
          arr.push(e.num)
        })
        // console.log("arr",arr);
        resolve(arr)
      })

    });
    const d = await Promise.all(dataMap)
    result.push(...d)
  }

  console.log("result", result);
  var name = `${req.auth.username}_students.xlsx`;
  var buffer = xlsx.build([{ name: 'students', data: result }]);

  // var buffer = new ArrayBuffer(16)
  // var blob = new Blob([buffer])
  console.log(buffer);
  // res.ok({ buffer,name})
  // res.ok(buffer)
  // res.headers['content-disposition'] = `attachment;filename=${name}`; //设置文件名
  //前端允许Content-Disposition
  res.set("filename", name);

  res.send(buffer) // buffer流 前端设置响应为blob即可

  // res.ok({
  //     blob: buffer, name: name
  // })
  // const filePath = excelPath + '/' + name
  // fs.writeFile(filePath, buffer, { 'flag': 'w' }, err => {
  //   // res.download(excelPath + '/' + name, downerr => {
  //   //   console.log(downerr, '990909')
  //   // });
  //   if(err) res.cc("出错了")
  //   //创建可读流
  //   const cs = fs.createReadStream(filePath);
  //   cs.on("data", chunk => {
  //     console.log("chunk",chunk);
  //     res.write(chunk);
  //   })
  //   cs.on("end", () => {
  //     // res.status(200);
  //     // res.end();
  //     res.ok({name})
  //   })
  // })
  // res.writeHead(200, {
  //   // 指定文件类型
  //   // xlsx
  //   // 'Content-Type':"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  //   // docx
  //   // 'Content-Type': 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
  //   // pdf
  //   'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  // })
  //创建可读流
  // let readStream = fs.createReadStream(excelPath + '/' + name)
  // // // 将读取的结果以管道pipe流的方式返回
  // readStream.pipe(res)

}


// 文件添加
const fileAddStudents = async (req, res) => {
  console.log(req.file);
  // 手动判断是否上传了文章封面
  if (!req.file || req.file.fieldname !== 'students') return res.cc('表格文件是必选参数！')
  // 查询班级列表
  console.log(req.file.path);
  // 处理表格数据
  let excelContent = xlsx.parse(req.file.path);
  let map = {
    '学生编号': "student_id",
    '学生姓名': 'name',
    '班级编号': 'c_id',
    '学生班级': 'className',
    '家长电话': 'p_phone',
    '家长姓名': 'p_name',
    '成绩表': 'scoreList',
  }
  console.log(excelContent[0].data);
  let columnName = excelContent[0].data[0];
  let data = excelContent[0].data.slice(1);
  console.log(columnName);
  console.log(data);
  let resString = ``
  const newData = []
  for (let i in data) {
    const list = data[i]
    // 跳过空行
    if(list.length<=0){
      continue;
    }
    let json = {
      name: '',
      p_phone: '',
      c_id: '',
    };
    // list.forEach((item, index) => {
    //   let key = map[columnName[index]];
    //   json[key] = item + '';
    // })
    let item = null
    for (let j in list) {
      item = list[j];
      let key = map[columnName[j]];
      if (key === 'scoreList') {
        json[key] = list.slice(j);
        break;
      }
      json[key] = item + '';
    }
    console.log(json);
    newData.push(json);
    // 进入班级和成绩处理函数

    // return 
    // 响应字符
    // const res = await xlsxDB(json)
    // console.log(res);
    // resString += res.resString
    // console.log(new Date().getTime());
  }
  console.log("180", newData);

 const d = await nameToId(newData)
  console.log("d===",d);
  // console.log(new Date().getTime(), "这是响应位置");
  // res.ok(null, resString)
  res.ok(d,"上传成功")
}

// 读取基本数据 班级列表 + 课程列表
async function nameToId(data) {
  const classRes = await selectAllClass()
  const courseRes = await selectAllCourse()
  // console.log("classRes",classRes);
  // console.log("courseRes",courseRes);
  let item = null
  let resData = []
  let nullStr = ``
  for (let i in data) {
    item = data[i]
    const nowClass = classRes.find((e) => e.className === item.className)
    console.log("nowClass==============",nowClass);
    item.c_id = nowClass ? nowClass.id : null
    if(item.c_id==null){
      nullStr +=  `${item.className}-` 
    }
  }
  if(nullStr.length>0){
   return`${nullStr}等班级不存在，请添加后重试`
  }
  for(let i in data){
    item = data[i]
    let item2 = null
    let scoreList = []
    for (let j = 0; j < item.scoreList.length; j += 2) {
      item2 = item.scoreList[j]
      const nowCourse = courseRes.find((e) => e.name === item2)
      item.scoreList[j] = nowCourse ? nowCourse.id : null
      if(item.scoreList[j]===null){
        continue;
      }
      scoreList.push({ course_id: item.scoreList[j], num: item.scoreList[j + 1] })
    }
    item.scoreList = scoreList
    console.log(i + "====", item);
    const res = await xlsxDBStudent(item)
    if (res.status === 1) {
      console.log(res);
      resData.push(res)
      continue;
    }
    // 成绩表添加
    const resD = await xlsxDBCourseList(scoreList, res.student_id)
    if (resD.length > 0) {
      resData.push(resD)
    }
  }
  console.log("data===", data);
  // res.ok(resData)
  // 先添加学生信息
  return resData
}

// 学生添加
function xlsxDBStudent(json) {
  return new Promise((resolve, reject) => {
    // 查询该学生是否存在
    const sql = `select * from t_student where name=? and p_phone=?`
    // 统一响应格式
    const res = {
      status: 0, // 1为失败
      message: "",
      err: null,
      errData: {}, // 失败的行数据
      student_id: null
    }
    db.query(sql, [json.name, json.p_phone], (err, results) => {
      if (err) {
        // resString += (JSON.stringify(json) + "查找失败\n")
        res.status = 1
        res.message = "sql出错了，请稍后重试"
        res.err = err
        res.errData = json
        reject(res)
      }
      if (results.length > 0) {
        res.student_id = results[0].id
        // 更新
        const sql = `update t_student set c_id=? where name=? and p_phone=?`
        db.query(sql, [json.c_id, json.name, json.p_phone], (err, results) => {
          if (err) {
            // resString += (JSON.stringify(json) + "更新失败\n")
            res.status = 1
            res.message = "update出错了，请稍后重试"
            res.err = err
            res.errData = json
            return reject(res)
          }
          resolve(res)
        })

      } else {
        const sql = `insert into t_student set ?`
        // delete json.className
        // if(json.c_id){
        //   res.status = 1
        //   res.message = "c_id为空"
        //   res.err = err
        //   res.errData = json
        //   reject(res)
        // }
        const body = { name: json.name, c_id: json.c_id, p_phone: json.p_phone, p_name: json.p_name }
        console.log("body==", body);
        db.query(sql, body, (err, results) => {
          if (err) {
            // resString += (JSON.stringify(json) + "添加失败\n")
            res.status = 1
            res.message = "insert出错了，请稍后重试"
            res.err = err
            res.errData = json
            return reject(res)
          }
          console.log("我要的id", results);
          res.student_id = results.insertId
          resolve(res)

          // resolve({ data: results, resString })
        })
      }
    })
  })
}
// 学生成绩添加
 function xlsxDBCourseList(list, student_id) {
   return new Promise(async  (resolve, reject) => {
    let item = null
    let errD = []
    for (let i in list) {
      item = list[i]
     await new Promise((resolve,reject)=>{
       db.query(`select * from t_score where student_id=? and course_id=?`,
         [student_id, item.course_id],
         (err, result) => {
           if (result.length > 0) {
             // 插入
             db.query(`update t_score set num=? where student_id=? and course_id=?`,
               [item.num, student_id, item.course_id],
               (err, result) => {
                 if (err) return errD.push({ student: { student_id, ...item }, err })
                 console.log({ student_id, ...item }, "更新成功");
                 resolve()
               })
           } else {
             // 插入
             db.query(`insert into t_score set ?`, { student_id, ...item }, (err, result) => {
               if (err) return errD.push({ student: { student_id, ...item }, err })
               console.log({ student_id, ...item }, "新增成功");
               resolve()
             })
           }
         })
     }) 
    }
    resolve(errD)
  })
}

const getList = async (req, res) => {
  const data = await getListItem(req, res)
  console.log("data", data);
  return typeof data === Error ? res.cc(data) : res.ok(data)
}
const getStudentById = async (req, res) => {
  const data = await getStudentByIdData(req.params.student_id)
  if (typeof data === Error) return res.cc(data)
  return res.ok(data)
}
const getStudentByIdData = (id) => {
  const sql = `SELECT ts.*,tc.className FROM t_student AS ts,t_class AS tc WHERE ts.id=? and ts.c_id = tc.id`
  return new Promise((resolve, reject) => {
    db.query(sql, id, (err, results) => {
      if (err) return reject(err)
      resolve(results)
    })
  })
}
const getListItem = (req, res) => {
  return new Promise((resolve, reject) => {
    const { class_id } = req.params
    console.log(class_id);
    if (class_id !== "all" && class_id != null && class_id != 'null') {
      console.log("if");
      const sql = `SELECT ts.*,tc.className FROM t_student AS ts,t_class AS tc WHERE ts.c_id=? and ts.c_id = tc.id`
      db.query(sql, class_id, (err, results) => {
        if (err) return reject(err)
        resolve(results)
      })
    } else if (class_id === "all") {
      console.log("else all");

      const sql = `SELECT ts.*,tc.className FROM t_student AS ts,t_class AS tc WHERE ts.c_id = tc.id`
      console.log(req.params);
      db.query(sql, (err, results) => {
        if (err) return reject(err)
        resolve(results)
      })
    } else {
      console.log("空模板");
      resolve([])
    }
  })
}

// 添加学生
const addStudent = (req, res) => {
  // console.log(req.body);
  const { p_phone, name } = req.body
  // 查询数据库中姓名+手机号码 是否重复
  const sql = `select * from t_student where p_phone=? and name=?`
  db.query(sql, [p_phone, name], (err, results) => {
    // console.log(results);
    if (err) return res.cc(err)
    if (results.length > 0) {
      return res.ok(null, "该学生信息已经存在！")
    }
    const sql = `insert into t_student set ?`
    db.query(sql, req.body, async (err, results) => {
      // console.log(results);

      if (err) return res.cc(err)
      if (results.affectedRows < 1) {
        return res.cc("添加失败，请稍后重试")
      }
      // console.log(results);
      // return res.ok(null);
      const data = await getStudentByIdData(results.insertId)
      if (typeof data === Error) return res.cc(data)
      return res.ok(data, "添加成功")
    })
  })
}

// 更新
const putStudent = (req, res) => {
  // console.log(req.body);
  const { p_phone, name, c_id } = req.body
  // 查询数据库中姓名+手机号码 是否重复
  const sql = `update t_student set ? where id = ?`
  db.query(sql, [{ p_phone, name, c_id }, req.body.id], async (err, results) => {
    // console.log(results);
    if (err) return res.cc(err)
    if (results.affectedRows < 1) {
      return res.cc("修改失败，请稍后重试")
    }
    const data = await getStudentByIdData(req.body.id)
    if (typeof data === Error) return res.cc(data)
    return res.ok(data, "修改成功")
  })

}

const delStudent = (req, res) => {
  const sql = `delete  from t_student where id=?`
  db.query(sql, req.params.id, (err, results) => {
    if (err) return res.cc(err)
    if (results.affectedRows < 1) {
      return res.cc("删除失败，该学生可能不存在")
    }
    return res.ok(null, "删除成功")
  })
}
module.exports = {
  getList, addStudent, putStudent, delStudent, fileAddStudents, fileGetStudents, getStudentById,
}